﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.IO;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Text;
using System.Drawing;
using System.Drawing.Imaging;
using System.ComponentModel;
using System.Drawing.Drawing2D;

namespace Village_Project
{
    public partial class education : System.Web.UI.Page
    {
        
  protected void Page_Load(object sender, EventArgs e)
        {

            if (Session["CID"] == null)
            {

                MessageBoxurl("กรุณากดค้นหา หรือลงทะเบียนเด็กก่อน", "findchild.aspx");

            }
            else
            {


                loaddataeducationinfoshow();


                if (!IsPostBack)
                {
                    loaddataEducationLevelRBL();
                    BindDatareg_educationnfo();
                    readtodatatableeducation();
                    loaddataeducationinfo();
                    loaddataeducationinfo2();
                    BindDatareg_educationnfoshow();
                    BindDatareg_educationnfoshowread();
                    loaddataeducationinfoshow();
                  


                    show_Images();

                }
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            insertregdataeducationinfo();
            insertdatatodbeducation();
            //Panel2.Visible = true;
            //Panel1.Visible = false;


            Response.Redirect("education.aspx");
        }

        private void MessageBoxurl(string msg, string urld)
        {
            Label lbl = new Label();
            lbl.Text = "<script language='javascript'>" + Environment.NewLine + "window.alert('" + msg + "');" + "window.location.href='" + urld + "';" + "</script>";
            Page.Controls.Add(lbl);
        }
        public void BindDatareg_educationnfo()
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection scon = new SqlConnection(constr);


            //string svccode1 = svccode;
            scon.Open();
            SqlCommand cmd = new SqlCommand("SELECT EducationStatusID as code,EducationStatus as dec FROM EducationStatus", scon);
            //cmd.Parameters.AddWithValue("@svccode1", svccode);
            SqlDataReader reader = cmd.ExecuteReader();

            GridView1.DataSource = reader;
            GridView1.DataBind();
            scon.Close();

            readtodatatableeducation();
        }







        public void show_Images()
        {
            string sj = "";



            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection conn = new SqlConnection(constr);

            SqlCommand CmdSql2 = new SqlCommand("SELECT Child_Pic FROM [Person] where CID=@CID", conn);
            conn.Open();
            CmdSql2.Parameters.AddWithValue("@CID", Session["CID"]);

            SqlDataReader reader2 = CmdSql2.ExecuteReader();

            if (reader2.Read())
            {
                sj = reader2["Child_Pic"].ToString();
            }
            else
            {
                sj = "~/pic/profile/nopic.jpg";
            }
            conn.Close();
            reader2.Close();


            Image1.ImageUrl = (sj);
        }





        protected void check1st()
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection conn = new SqlConnection(constr);
            //ถ้าว่างแสดงว่ามาครั้งแรก
            SqlCommand CmdSql2 = new SqlCommand("SELECT CID FROM [educationinfo] where CID=@CID and lastupdate is not null", conn);
            conn.Open();
            CmdSql2.Parameters.AddWithValue("@CID", Session["CID"]);

            SqlDataReader reader2 = CmdSql2.ExecuteReader();



            if (reader2.Read())
            {
                Panel2.Visible = true;


            }
            else
            {
                Panel1.Visible = true;
            }
            conn.Close();
            reader2.Close();



        }





        public void readtodatatableeducation()
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection scon = new SqlConnection(constr);


            scon.Open();
            DataTable dtedu = new DataTable();

            SqlCommand cmd = new SqlCommand("SELECT code,Note from education where CID=@CID", scon);
            cmd.Parameters.AddWithValue("@CID", Session["CID"]);

            SqlDataReader reader8 = cmd.ExecuteReader();

            dtedu.Columns.Add("code");
            dtedu.Columns.Add("Note");
            while (reader8.Read() != false)
            {
                dtedu.Rows.Add(reader8["code"].ToString(), (reader8["Note"].ToString()));
                //x3.Add(reader8["ACTCode"].ToString());
                //x4.Add(reader8["Note"].ToString());

            }
            reader8.Close();


            for (int j = 0; j < dtedu.Rows.Count; j++)
            {
                string a = dtedu.Rows[j]["code"].ToString();
                foreach (GridViewRow GR in GridView1.Rows)
                {
                    string b = GR.Cells[0].Text.ToString();
                    CheckBox chk = (CheckBox)GR.FindControl("CaseSelectoredu");
                    TextBox tb = (TextBox)GR.FindControl("TextBoxedu");
                    if (a == b)
                    {

                        tb.Text = dtedu.Rows[j]["Note"].ToString();
                        // tb.Text=
                        chk.Checked = true;
                    }
                }
            }


            scon.Close();




        }





        //eduinfo สร้างตารางโชว์ข้อมูล
        public void BindDatareg_educationnfoshow()
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection scon = new SqlConnection(constr);


            //string svccode1 = svccode;
            scon.Open();
            SqlCommand cmd = new SqlCommand("SELECT a.EducationStatusID as code,a.EducationStatus as dec FROM EducationStatus a join education b on b.code=a.EducationStatusID where b.CID=@CID ", scon);
            cmd.Parameters.AddWithValue("@CID", Session["CID"]);
            //cmd.Parameters.AddWithValue("@svccode1", svccode);
            SqlDataReader reader = cmd.ExecuteReader();

            GridView2.DataSource = reader;
            GridView2.DataBind();
            scon.Close();
            BindDatareg_educationnfoshowread();

        }



        //อ่านค่าเพื่อไปโชว์ใน label


        public void BindDatareg_educationnfoshowread()
        {



            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection scon = new SqlConnection(constr);


            scon.Open();
            DataTable dtedu22 = new DataTable();

            SqlCommand cmd = new SqlCommand("SELECT code,Note from education where CID=@CID", scon);
            cmd.Parameters.AddWithValue("@CID", Session["CID"]);

            SqlDataReader reader8 = cmd.ExecuteReader();

            dtedu22.Columns.Add("code");
            dtedu22.Columns.Add("Note");
            while (reader8.Read() != false)
            {
                dtedu22.Rows.Add(reader8["code"].ToString(), (reader8["Note"].ToString()));
                //x3.Add(reader8["ACTCode"].ToString());
                //x4.Add(reader8["Note"].ToString());

            }
            reader8.Close();


            for (int j = 0; j < dtedu22.Rows.Count; j++)
            {
                string a = dtedu22.Rows[j]["code"].ToString();
                foreach (GridViewRow GR in GridView2.Rows)
                {
                    string b = GR.Cells[0].Text.ToString();
                    //CheckBox chk = (CheckBox)GR.FindControl("CaseSelectoredu");
                    Label tb = (Label)GR.FindControl("Labeledu");
                    if (a == b)
                    {

                        tb.Text = dtedu22.Rows[j]["Note"].ToString();
                        // tb.Text=
                        //chk.Checked = true;
                    }
                }
            }


            scon.Close();







        }






        public void insertregdataeducationinfo()
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection conn2 = new SqlConnection(constr);


            SqlCommand CmdSql = new SqlCommand("UPDATE [EducationChild] SET SchoolName=@School,EducationStatusID=@level,Class=@type,NameofHumanAtSchool=@Personnel,TelSchool=@phone,lastupdate=@lastupdate WHERE CID=@CID", conn2);
            conn2.Open();
            CmdSql.Parameters.AddWithValue("@School", School.Text);
            CmdSql.Parameters.AddWithValue("@level", EducationLevelRBL.SelectedValue);
            CmdSql.Parameters.AddWithValue("@type", type.Text);
            CmdSql.Parameters.AddWithValue("@Personnel", Personnel.Text);
            CmdSql.Parameters.AddWithValue("@phone", phone.Text);
            CmdSql.Parameters.AddWithValue("@CID", Session["CID"]);
            CmdSql.Parameters.AddWithValue("@lastupdate", DateTime.Now);
            CmdSql.ExecuteNonQuery();
            conn2.Close();
            //MessageBox("ลงทะเบียนเรียบร้อยครับ");  lastupdate
            //ImageUpload_f();
            // Response.Redirect("~/regfamily.aspx");
        }





        //โหลด db to textbox
        public void loaddataeducationinfo()
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection conn = new SqlConnection(constr);

            SqlCommand CmdSql2 = new SqlCommand("SELECT [CID],[EducationStatusID],[SchoolName],[Class],[NameofHumanAtSchool],[TelSchool] FROM [EducationChild] where CID=@CID", conn);
            conn.Open();
            CmdSql2.Parameters.AddWithValue("@CID", Session["CID"]);

            SqlDataReader reader22 = CmdSql2.ExecuteReader();



            if (reader22.Read())
            {
                School.Text = reader22["SchoolName"].ToString();
          
                type.Text = reader22["Class"].ToString();
                Personnel.Text = reader22["NameofHumanAtSchool"].ToString();
                phone.Text = reader22["TelSchool"].ToString();



            }
            else
            {

            }
            conn.Close();
            reader22.Close();

        }



        //โหลด db to radip
        public void loaddataeducationinfo2()
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection conn = new SqlConnection(constr);

            SqlCommand CmdSql2 = new SqlCommand("SELECT [CID],[EducationStatusID],[SchoolName],[Class],[NameofHumanAtSchool],[TelSchool] FROM [EducationChild] where CID=@CID", conn);
            conn.Open();
            CmdSql2.Parameters.AddWithValue("@CID", Session["CID"]);

            SqlDataReader reader2 = CmdSql2.ExecuteReader();



            if (reader2.Read())
            {
                EducationLevelRBL.SelectedValue = reader2["EducationStatusID"].ToString();



            }
            else
            {

            }
            conn.Close();
            reader2.Close();

        }














        public void insertdatatodbeducation()
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection scon = new SqlConnection(constr);

            //bool selectorChecked4 = false;

            DataTable dt2edu = new DataTable();
            dt2edu.Columns.Add("SACTCode");

            dt2edu.Columns.Add("note");
            foreach (GridViewRow row in GridView1.Rows)
            {
                CheckBox cb = (CheckBox)row.FindControl("CaseSelectoredu");
                if (cb != null && cb.Checked)
                {
                    //selectorChecked4 = true;
                    TextBox rb = row.FindControl("TextBoxedu") as TextBox;
                    DataRow dr2edu;
                    dr2edu = dt2edu.NewRow();
                    string SACTCode = GridView1.Rows[row.RowIndex].Cells[0].Text;
                    // string CID = GridView2.Rows[row.RowIndex].Cells[1].Text;
                    //SelectStat.Text += string.Format("{0} has been added <br />", SACTCode);
                    //  string CID = GridView1.Rows[row.RowIndex].Cells[1].Text;
                    string note = rb.Text;
                    dt2edu.Rows.Add(SACTCode, note);


                }
            }




            SqlCommand command2 = new SqlCommand("delete from [education] where  CID=@CID", scon);
            scon.Open();
            command2.Parameters.AddWithValue("@CID", Session["CID"]);
            command2.ExecuteNonQuery();
            scon.Close();



            for (int i = 0; i < dt2edu.Rows.Count; i++)
            {


                SqlCommand command = new SqlCommand("INSERT INTO [education] (code,CID,Note,[update]) VALUES('" + dt2edu.Rows[i]["SACTCode"].ToString() + "',@PSNumber,'" + dt2edu.Rows[i]["note"].ToString() + "',@update)", scon);
                scon.Open();
                command.Parameters.AddWithValue("@PSNumber", Session["CID"]);
                command.Parameters.AddWithValue("@update", DateTime.Now);
                command.ExecuteNonQuery();
                scon.Close();

                SqlCommand commandlog = new SqlCommand("INSERT INTO [education_log] (code,CID,Note,[update]) VALUES('" + dt2edu.Rows[i]["SACTCode"].ToString() + "',@PSNumber,'" + dt2edu.Rows[i]["note"].ToString() + "',@update)", scon);
                scon.Open();
                commandlog.Parameters.AddWithValue("@PSNumber", Session["CID"]);
                commandlog.Parameters.AddWithValue("@update", DateTime.Now);
                commandlog.ExecuteNonQuery();
                scon.Close();



            }




            BindDatareg_educationnfo();

        }












        //label

        public void loaddataeducationinfoshow()
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection conn = new SqlConnection(constr);

            SqlCommand CmdSql2 = new SqlCommand("SELECT [CID],[EducationStatusID],[SchoolName],[Class],[NameofHumanAtSchool],[TelSchool] FROM [EducationChild] where CID=@CID", conn);
            conn.Open();
            CmdSql2.Parameters.AddWithValue("@CID", Session["CID"]);

            SqlDataReader reader2 = CmdSql2.ExecuteReader();



            if (reader2.Read())
            {





                School0.Text = reader2["SchoolName"].ToString();
                level0.Text = reader2["EducationStatusID"].ToString();
                type0.Text = reader2["Class"].ToString();
                Personnel0.Text = reader2["NameofHumanAtSchool"].ToString();
                phone0.Text = reader2["TelSchool"].ToString();
                joinEducationLevel();


            }
            else
            {

            }
            conn.Close();
            reader2.Close();

        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            Panel1.Visible = true;
            Panel2.Visible = false;
        }







        public void loaddataEducationLevelRBL()
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection conn = new SqlConnection(constr);

            SqlCommand CmdSql2 = new SqlCommand("SELECT EducationLevelID,EducationLevelDescription FROM EducationLevel", conn);
            conn.Open();

            SqlDataReader reader4 = CmdSql2.ExecuteReader();

            DataTable dtBloodTypeRBL = new DataTable();
            EducationLevelRBL.Items.Clear();
            ListItem newItem = new ListItem();


            while (reader4.Read())
            {
                newItem = new ListItem();
                newItem.Text = reader4["EducationLevelDescription"].ToString();
                newItem.Value = reader4["EducationLevelID"].ToString();
                EducationLevelRBL.Items.Add(newItem);
            }

        }









        protected void joinEducationLevel()
        {


            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection conn = new SqlConnection(constr);


            SqlCommand CmdSql2 = new SqlCommand("SELECT  Province.[EducationLevelDescription] as LiveCity  FROM [EducationLevel] Province join [EducationChild] Person on Person.EducationStatusID=Province.EducationLevelID  where Person.CID=@CID", conn);
            conn.Open();
            CmdSql2.Parameters.AddWithValue("@CID", Session["CID"]);


            SqlDataReader reader2 = CmdSql2.ExecuteReader();

            if (reader2.Read())
            {
                level0.Text = reader2["LiveCity"].ToString();
            }
            else
            {

            }
            conn.Close();
            reader2.Close();


        }







    }
}